﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Nhibernate;
using NHibernate.Criterion;
using System.Data.SqlClient;
using NHibernate.Cfg;
using NHibernate;
using System.Data;

namespace Bussiness.Service
{
    public class Order_Service
    {
        public List<HeaderOrder> SearchOrder(HeaderOrder header)
        {
            HibernateDAO<HeaderOrder, String> dao = new HibernateDAO<HeaderOrder, String>();

            if (!Formater.isNull(header.PhoneCus))
                dao.addSearchCriteria(Expression.Like("PhoneCus", header.PhoneCus, MatchMode.Anywhere).IgnoreCase());
            if (header.Date != null)
                dao.addSearchCriteria(Expression.Eq("Date", header.Date));
            if (!Formater.isNull(header.NameCus))
                dao.addSearchCriteria(Expression.Like("NameCus", header.NameCus, MatchMode.Anywhere).IgnoreCase());

            return dao.getByCriterion();
        }

        public DateTime GetLatestOrder()
        {
            DateTime o ;
            try
            {

                string connect = System.Configuration.ConfigurationManager.ConnectionStrings["VATConnectionString"].ToString();
                SqlConnection scn = new SqlConnection(connect);
                scn.Open();
                SqlCommand spcmd = new SqlCommand("Select max(date) from Header_Order", scn);
                spcmd.CommandType = CommandType.Text;
                SqlDataAdapter da = new SqlDataAdapter(spcmd);


                o = (DateTime) spcmd.ExecuteScalar();



                scn.Close();
            }


            catch (SqlException x)
            {
                throw;
            }

            return o; 
        }
        public HeaderOrder GetOrder(int id)
        {

            HibernateDAO<HeaderOrder, String> dao = new HibernateDAO<HeaderOrder, String>();
            HibernateDAO<DetailOrder, String> dao2 = new HibernateDAO<DetailOrder, String>();

            dao.addSearchCriteria(Expression.Eq("ID", id));
            dao2.addSearchCriteria(Expression.Eq("IdHeader", id));
            List<HeaderOrder> temp = dao.getByCriterion();
            List<DetailOrder> temp2 = dao2.getByCriterion();
            foreach (HeaderOrder sv in temp)
            {
                sv.ListDetail1 = temp2;
                return sv;
            }
            return null;
        }

        public List<HeaderOrder> GetOrderByUser(string name,int thang)
        {
            List<HeaderOrder> lstTemp = null;
            ITransaction tran = null;
            ISession hsession = null;
            try
            {
                hsession = NHibernateSessionManager.GetCurrentSession();
                if(thang != 13)
                    lstTemp = (List<HeaderOrder>)hsession.CreateSQLQuery(@"select {p.*} from Header_Order {p} where month(p.date) = '" + thang + "' and p.name_imployee = '" + name + "'order by (p.date) asc").AddEntity("p", typeof(HeaderOrder)).List<HeaderOrder>();
                else
                    lstTemp = (List<HeaderOrder>)hsession.CreateSQLQuery(@"select {p.*} from Header_Order {p} where  year(p.date) = year(getdate()) and p.name_imployee = '" + name + "'order by (p.date) asc").AddEntity("p", typeof(HeaderOrder)).List<HeaderOrder>();
            }
            catch (Exception e) { Console.WriteLine(e.Message); }
            //finally { hsession.Flush(); hsession.Close(); }

            return lstTemp;
        }

        public List<HeaderOrder> GetOrderByMonth(int thang)
        {
            List<HeaderOrder> lstTemp = null;
            ITransaction tran = null;
            ISession hsession = null;
            try
            {
                hsession = NHibernateSessionManager.GetCurrentSession();
                if (thang != 13)
                    lstTemp = (List<HeaderOrder>)hsession.CreateSQLQuery(@"select {p.*} from Header_Order {p} where month(p.date) = '" + thang + "' order by (p.date) asc").AddEntity("p", typeof(HeaderOrder)).List<HeaderOrder>();
                else
                    lstTemp = (List<HeaderOrder>)hsession.CreateSQLQuery(@"select {p.*} from Header_Order {p} where  year(p.date) = year(getdate()) order by (p.date) asc").AddEntity("p", typeof(HeaderOrder)).List<HeaderOrder>();
            }
            catch (Exception e) { Console.WriteLine(e.Message); }
            //finally { hsession.Flush(); hsession.Close(); }

            return lstTemp;
        }


        public void deleteHoaDon(int idHeader)
        {

            ISession session = null;
            ITransaction transaction = null;
            try
            {
                session = NHibernateSessionManager.GetCurrentSession();
                transaction = session.BeginTransaction();


                IQuery query = session.CreateSQLQuery("delete Header_Order  where id = :id").AddEntity(typeof(HeaderOrder));
                query.SetInt32("id", idHeader);
                query.ExecuteUpdate();

                IQuery query2 = session.CreateSQLQuery("delete Detail_Order  where id_header = :id").AddEntity(typeof(DetailOrder));
                query2.SetInt32("id", idHeader);
                query2.ExecuteUpdate();


                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw ex;
            }
            finally
            {
                NHibernateSessionManager.CloseSession();
            }
            
           
        }
    

    }
}
